using Dapper;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Reflection;
using System.Text;

namespace Dapper.Dal
{
    public abstract partial class AbstractDbClient
    {
        public int Execute(string sql, object param = null, int? commandTimeout = null, CommandType? commandType = null)
        {
            DbConnection connection = null;
            try
            {
                connection = CreateAndOpenConnection();
                return connection.Execute(sql, param, Transaction, commandTimeout ?? DefaultWriteTimeout, commandType);
            }
            finally
            {
                if (connection != null)
                    CloseConnection(connection);
            }
        }

        public object Scalar(string sql, object param = null, int? commandTimeout = null, CommandType? commandType = null)
        {
            DbConnection connection = null;
            try
            {
                connection = CreateAndOpenConnection();
                return connection.ExecuteScalar(sql, param, Transaction, commandTimeout ?? DefaultReadTimeout, commandType);
            }
            finally
            {
                if (connection != null)
                    CloseConnection(connection);
            }
        }

        public T Scalar<T>(string sql, object param = null, int? commandTimeout = null, CommandType? commandType = null)
        {
            DbConnection connection = null;
            try
            {
                connection = CreateAndOpenConnection();
                return connection.ExecuteScalar<T>(sql, param, Transaction, commandTimeout ?? DefaultReadTimeout, commandType);
            }
            finally
            {
                if (connection != null)
                    CloseConnection(connection);
            }
        }

        public IEnumerable<object> List(Type type, string sql, object param = null, bool buffered = true, int? commandTimeout = null, CommandType? commandType = null)
        {
            DbConnection connection = null;
            try
            {
                connection = CreateAndOpenConnection();
                return connection.Query(type, sql, param, Transaction, buffered, commandTimeout ?? DefaultReadTimeout, commandType);
            }
            finally
            {
                if (connection != null)
                    CloseConnection(connection);
            }
        }

        public IEnumerable<T> List<T>(string sql, object param = null, bool buffered = true, int? commandTimeout = null, CommandType? commandType = null)
        {
            DbConnection connection = null;
            try
            {
                connection = CreateAndOpenConnection();
                return connection.Query<T>(sql, param, Transaction, buffered, commandTimeout ?? DefaultReadTimeout, commandType);
            }
            finally
            {
                if (connection != null)
                    CloseConnection(connection);
            }
        }

        public T Get<T>(string sql, object param = null, int? commandTimeout = null, CommandType? commandType = null)
        {
            DbConnection connection = null;
            try
            {
                connection = CreateAndOpenConnection();
                return connection.QueryFirstOrDefault<T>(sql, param, Transaction, commandTimeout ?? DefaultReadTimeout, commandType);
            }
            finally
            {
                if (connection != null)
                    CloseConnection(connection);
            }
        }

        public dynamic Get(string sql, object param = null, int? commandTimeout = null, CommandType? commandType = null)
        {
            DbConnection connection = null;
            try
            {
                connection = CreateAndOpenConnection();
                return connection.QueryFirstOrDefault(sql, param, Transaction, commandTimeout ?? DefaultReadTimeout, commandType);
            }
            finally
            {
                if (connection != null)
                    CloseConnection(connection);
            }
        }

        public IEnumerable<IDataRecord> Records(string sql, object param = null, int? commandTimeout = null, CommandType? commandType = null)
        {
            DbConnection connection = null;
            DbDataReader reader = null;
            try
            {
                connection = CreateAndOpenConnection();
                reader = (DbDataReader)connection.ExecuteReader(
                    sql, param, Transaction, commandTimeout ?? DefaultReadTimeout, commandType);
                return YieldRows(connection, reader);
            }
            finally
            {
                if (reader != null && !reader.IsClosed)
                    reader.Close();

                if (connection != null)
                    CloseConnection(connection);
            }
        }

        public object Get(Type type, string sql, object param = null, int? commandTimeout = null, CommandType? commandType = null)
        {
            DbConnection connection = null;
            try
            {
                connection = CreateAndOpenConnection();
                return connection.QueryFirstOrDefault(type, sql, param, Transaction, commandTimeout ?? DefaultReadTimeout, commandType);
            }
            finally
            {
                if (connection != null)
                    CloseConnection(connection);
            }
        }

        public GridReaderWapper QueryMultiple(string sql, object param = null, int? commandTimeout = null, CommandType? commandType = null)
        {
            DbConnection connection = null;
            try
            {
                connection = CreateAndOpenConnection();
                return new GridReaderWapper(
                    connection.QueryMultiple(sql, param, Transaction, commandTimeout ?? DefaultReadTimeout, commandType),
                    connection, Transaction != null);
            }
            catch (Exception)
            {
                if (connection != null)
                    CloseConnection(connection);

                throw;
            }
        }

        public string GetParas<T>()
        {
            StringBuilder columns = new StringBuilder();
            Type t = typeof(T);
            PropertyInfo[] properties = t.GetProperties();
            foreach (var p in properties)
            {
                if (p.PropertyType.Name == "IList`1" || p.PropertyType.Name.ToUpper().Contains("LIST") || p.Name.ToUpper() == "ID")
                {
                    continue;
                }
                columns.Append(p.Name).Append(",");
            }
            return columns.Remove(columns.Length - 1, 1).ToString();
        }

        public string GetATParas<T>()
        {
            StringBuilder columns = new StringBuilder();
            Type t = typeof(T);
            PropertyInfo[] properties = t.GetProperties();
            foreach (var p in properties)
            {
                if (p.PropertyType.Name == "IList`1" || p.PropertyType.Name.ToUpper().Contains("LIST") || p.Name.ToUpper() == "ID")
                {
                    continue;
                }
                columns.Append("@").Append(p.Name).Append(",");
            }
            return columns.Remove(columns.Length - 1, 1).ToString();
        }

        public string GetParasToAT<T>()
        {
            StringBuilder columns = new StringBuilder();
            Type t = typeof(T);
            PropertyInfo[] properties = t.GetProperties();
            foreach (var p in properties)
            {
                if (p.PropertyType.Name == "IList`1" || p.PropertyType.Name.ToUpper().Contains("LIST") || p.Name.ToUpper() == "ID")
                {
                    continue;
                }
                columns.Append(p.Name).Append("=@" + p.Name + ",");
            }
            return columns.Remove(columns.Length - 1, 1).ToString();
        }

        public string GetParasToAT<T>(T model)
        {
            StringBuilder columns = new StringBuilder();
            Type t = typeof(T);
            PropertyInfo[] properties = t.GetProperties();
            foreach (var p in properties)
            {
                if (p.PropertyType.Name == "IList`1" || p.PropertyType.Name.ToUpper().Contains("LIST") || p.Name.ToUpper() == "ID")
                {
                    continue;
                }
                object value = p.GetValue(model, null);
                if (!string.IsNullOrEmpty(value?.ToString()))
                {
                    columns.Append(p.Name).Append("=@" + p.Name + ",");
                }
            }
            return columns.Remove(columns.Length - 1, 1).ToString();
        }   
    }
}